#Loading libraries
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

#Ignoring warnings
import warnings
warnings.filterwarnings("ignore")

#Reading the data
data = pd.read_excel('FA Analysis_Rocket.xlsx')

#No of columns
len(data.columns)

118

#No of rows
len(data)

4186

#Widening the display of columns and rows so as to see all the 118 columns
pd.options.display.max_columns = None
pd.options.display.max_rows = None

#Quick glance of the dataset
data.head()

# Number of each type of column in the provided dataset
data.dtypes.value_counts()

object     88
float64    18
int64      12
dtype: int64

Data Cleaning¶

#checking for duplicated rows

print('Any duplicated rows?', data.duplicated().any())

Any duplicated rows? False

# The following code checks for duplicated column values
# This checks for structural errors when different column names were given for the same data

dup_cols = {}
for i, col1 in enumerate(data.columns):
    for col2 in data.columns[i+1:]:
        if col2 not in dup_cols and np.all(data[col1] == data[col2]):
            dup_cols[col2] = col1
print('Any duplicated columns?', bool(dup_cols))

Any duplicated columns? False

data.select_dtypes(include=['object']).head()

# Checking for object type columns, appearing numeric if they contain '-' entries

(data == '-').any().any()

True

# Replacing '-' with NaN and converting those columns to numeric

data['Client Acquisition Details-YTD $1m+ QNRs (March-2018)'] = pd.to_numeric(data['Client Acquisition Details-YTD $1m+ QNRs (March-2018)'], errors='coerce')

data['Wealth/Financial Planning -H/UHNW MHH Planning %'] = pd.to_numeric(data['Wealth/Financial Planning -H/UHNW MHH Planning %'], errors='coerce')

data['Wealth/Financial Planning -Total MHHs'] = pd.to_numeric(data['Wealth/Financial Planning -Total MHHs'], errors='coerce')

# product mix 16-61 index

data.iloc[:,16:62] = data.iloc[:,16:62].apply(pd.to_numeric, errors='coerce')

# Client Age Distribution index 106-110
data.iloc[:, 106:111] = data.iloc[:, 106:111].apply(pd.to_numeric, errors='coerce')

# Maketing Details index 84-88
data.iloc[:, 84:89] = data.iloc[:, 84:89].apply(pd.to_numeric, errors='coerce')

# Segment Index Score 111-117
data.iloc[:, 111:118] = data.iloc[:, 111:118].apply(pd.to_numeric, errors='coerce')

# MTP 95-97
data.iloc[:, 95:98] = data.iloc[:, 95:98].apply(pd.to_numeric, errors='coerce')

data['Digital Tools-% of MHHs on OLS'] = pd.to_numeric(data['Digital Tools-% of MHHs on OLS'], errors='coerce')

data['Client Retention-% of Attrited MHHs (2017 - Post Split)'] = pd.to_numeric(data['Client Retention-% of Attrited MHHs (2017 - Post Split)'], errors='coerce')

# Client Acquisition Details 64-72
data.iloc[:, 64:73] = data.iloc[:, 64:73].apply(pd.to_numeric, errors='coerce')

# NNM 73-83
data['Net New Money Details-FA Comp. NNM 2014'] = pd.to_numeric(data['Net New Money Details-FA Comp. NNM 2014'], errors='coerce')
data['Net New Money Details-FA Comp. NNM 2015'] = pd.to_numeric(data['Net New Money Details-FA Comp. NNM 2015'], errors='coerce')
data['Net New Money Details-FA Comp. NNM 2016'] = pd.to_numeric(data['Net New Money Details-FA Comp. NNM 2016'], errors='coerce')
data['Net New Money Details-YTD NNM \n(March-2018)'] = pd.to_numeric(data['Net New Money Details-YTD NNM \n(March-2018)'], errors='coerce')

# confirming if we have removed all the '-' entries

(data == '-').any().any()

False

# Transforming some numerical variables that are really categorical

data['FA Details-Teaming Flag'] = data['FA Details-Teaming Flag'].astype(str)
data['FA Details-Production Quintile'] = data['FA Details-Production Quintile'].astype(str)
data['Maketing Details-FA Web'] = data['Maketing Details-FA Web'].astype(str)
data['Maketing Details-FA Social'] = data['Maketing Details-FA Social'].astype(str)

# Check for missing values

# The result is shown as a table defined as null_df for clarity. 
null_df = data.isnull().sum().reset_index()

# Define column labels for null_df
null_df.columns = ['column_name', 'null_count']

# Calculate the percentage of missing values
null_df['null_%'] = null_df['null_count']/len(data) * 100

# Display the columns in descending order of the percentage of missing values
null_df.sort_values('null_%', ascending=False).reset_index(drop=True)

# Number of missing values
np.count_nonzero(data.isnull())

19852

We see that there are many missing values in certain columns. To deal with that, there are a couple of ways:¶

Impute default values for the missing data

Remove the rows that have missing data

Remove the columns that have a high percetage of missing data (e.g. > 90%)

As all the columns have relatively low percetage of missing values, we would try to impute the missing values. Add default values To decide what to put in the missing values, we have to look at the properties of the data. For example, ‘FA Details-Rec. Council 2018’, 'FA Details-Sig Team.' and 'Branch Detail-Branch Wire' are categorical columns. Some of the FAs probably don’t have this information provided. In this case, we don’t want to assume the 'Rec. Council 2018','Sig Team.' and 'Branch Wire' for those FAs, so we will replace it with a default value 'Not Given'.

data['FA Details-Rec. Council 2018'] = data['FA Details-Rec. Council 2018'].fillna('Not Given')
data['FA Details-Sig Team.'] = data['FA Details-Sig Team.'].fillna('Not Given')
data['Branch Detail-Branch Wire'] = data['Branch Detail-Branch Wire'].fillna('Not Given')

# For the other numeric columns with missing values, we will fill them with 0, mean or median depending on the properties of the feature:


# Net new money
for col in ('Net New Money Details-FA Comp. NNM 2014',
            'Net New Money Details-FA Comp. NNM 2015',
            'Net New Money Details-FA Comp. NNM 2016',
            'Net New Money Details-YTD NNM \n(March-2018)'):
    data[col] = data[col].fillna(0)


# Client acquisition
for col in ('Client Acquisition Details-YTD $1m+ QNRs (March-2018)', 
            'Client Acquisition Details-$1M+ QNR\'s 2015', 
            'Client Acquisition Details-$1M+ QNR\'s 2016', 
            'Client Acquisition Details-$1M+ QNR\'s 2017',
            'Client Acquisition Details-QNR 2017',
            'Client Acquisition Details-QNR 2016',
            'Client Acquisition Details-QNR 2015'):
    data[col] = data[col].fillna(0)
    
# Marketing
for col in ('Maketing Details-Page Views', 
            'Maketing Details-News Feed Activity',
            'Maketing Details-Total AMC orders (12 month)'):
    data[col] = data[col].fillna(0)
    
data['Maketing Details-FA Web'] = data['Maketing Details-FA Web'].replace('nan', '0.0')
data['Maketing Details-FA Social'] = data['Maketing Details-FA Social'].replace('nan', '0.0')

# FA details    
data['FA Details-Yrs. Left on EFL\n(Feb 2018)'] = data['FA Details-Yrs. Left on EFL\n(Feb 2018)'].fillna(
    data['FA Details-Yrs. Left on EFL\n(Feb 2018)'].mean())
data['FA Details-Production Quintile'] = data['FA Details-Production Quintile'].replace('nan', '3.0') # fill with median


# Planning
for col in ('Wealth/Financial Planning -H/UHNW MHH Planning %', 
            'Wealth/Financial Planning -Total MHHs'):
    data[col] = data[col].fillna(0)

# Segment score    
for col in ('Segment Index Score (All FA Score)-Planning Score', 
            'Segment Index Score (All FA Score)-Advisory Score',
            'Segment Index Score (All FA Score)-Client Retention'):
    data[col] = data[col].fillna(0)
    
# Product mix
data.iloc[:, 16:62] = data.iloc[:, 16:62].fillna(0)

# Client age
data.iloc[:,106:111] = data.iloc[:,106:111].fillna(0)

# Client retention
data['Client Retention-% of Attrited MHHs (2017 - Post Split)'] = data['Client Retention-% of Attrited MHHs (2017 - Post Split)'].fillna(0)

# Digital tools
for col in ('-% of MHHs on MTP', # highly right-skewed distribution
            '-# accts enrolled in MTP',
            '-Outside Assets in MTP'):
    data[col] = data[col].fillna(0)
data['Digital Tools-% of MHHs on OLS'] = data['Digital Tools-% of MHHs on OLS'].fillna(data['Digital Tools-% of MHHs on OLS'].mean())

# Confirm that we have filled all mising values
data.isnull().any().any()

False

#  Brief summary of dataset
data.describe()

Check for outliers¶

Outliers affect our models, particularly for linear models. One common way of performing outlier detection is to assume that the regular data come from a known distribution (e.g. data are Gaussian distributed). Then we can establish a threshold that defines an observation as an outlier. To do so, we'll standardize the data, which means converting data values to have mean of 0 and a standard deviation of 1. To handle outliers, we can tranform the features using for e.g. log transformation when the values are positive. In this case, we have negative values so we cannot do log tranformation on the features. So in the later analysis, we will check the features in detail and see if we can think of dropping these observations.

Features with constant values¶

For the training set there are 12 features without any variation. We won’t be able to train our classifiers on these features, so we might as well discard them.

# Counts the feature with unique values

feature_count = data.nunique(dropna=False) # treat NaNs as a distinct value
feature_count

FA Details-FAID                                                   4186
FA Details-I-LOS                                                   549
FA Details-Rocket-LOS                                              475
FA Details-I-LOS Band                                                9
FA Details-Rocket-LOS Band                                           9
FA Details-Sig Team.                                               940
FA Details-Teaming Flag                                              2
FA Details-Rec. Council 2018                                         7
FA Details-Rocket Lifecycle Stage                                    9
FA Details-Yrs. Left on EFL\n(Feb 2018)                            199
FA Details-Production Quintile                                       5
FA Details-T12\n(Feb 2018)                                        4185
FA Details-AuM YE 17                                              4138
FA Details-AuM YE Band                                               9
Branch Detail-Branch Wire                                          289
Branch Detail-Division                                               4
Product Mix (Rev)-Advisory %                                      4109
Product Mix (Rev)-Alts.                                           2894
Product Mix (Rev)-BANKING                                         4148
Product Mix (Rev)-CREDIT LINE                                     4007
Product Mix (Rev)-EQUITIES                                        4093
Product Mix (Rev)-INSURANCE / ANNUITIES                           3839
Product Mix (Rev)-LENDING                                          800
Product Mix (Rev)-MISCELLANEOUS                                   2810
Product Mix (Rev)-MUNICIPALS                                      2457
Product Mix (Rev)-MF and UIT                                      4141
Product Mix (Rev)-OTHER                                           4152
Product Mix (Rev)-STRUCTURED PROD                                 1435
Product Mix (Rev)-TAXABLE FIXED I                                 3359
Product Mix (Rev)-ADVISORY                                        4102
Product Mix (Rev)-Alts..1                                         2796
Product Mix (Rev)-BANKING.1                                       4122
Product Mix (Rev)-CREDIT LINE.1                                   3968
Product Mix (Rev)-EQUITIES.1                                      4057
Product Mix (Rev)-INSURANCE / ANNUITIES.1                         3825
Product Mix (Rev)-LENDING.1                                        655
Product Mix (Rev)-MISCELLANEOUS.1                                 2635
Product Mix (Rev)-MUNICIPALS.1                                    2217
Product Mix (Rev)-MF and UIT.1                                    4134
Product Mix (Rev)-OTHER.1                                         4149
Product Mix (Rev)-STRUCTURED PROD.1                               1266
Product Mix (Rev)-TAXABLE FIXED I.1                               3111
Product Mix (Assets)-Wrap %                                       4038
Product Mix (Assets)-CASH                                         4100
Product Mix (Assets)-COMMODITIES                                  3655
Product Mix (Assets)-EQUITY                                       4100
Product Mix (Assets)-FIXED INCOME                                 4079
Product Mix (Assets)-NON-TRADITIONAL                              3937
Product Mix (Assets)-OTHER                                        4078
Product Mix (Assets)-OUTSIDE                                      3071
Product Mix (Assets)-PB ASSET                                      123
Product Mix (Assets)-THIRD PARTY                                  3536
Product Mix (Assets)-Wrap                                         3973
Product Mix (Assets)-CASH .1                                      4092
Product Mix (Assets)-COMMODITIES.1                                3433
Product Mix (Assets)-EQUITY.1                                     4074
Product Mix (Assets)-FIXED INCOME.1                               4042
Product Mix (Assets)-NON-TRADITIONAL.1                            3832
Product Mix (Assets)-OTHER.1                                      4042
Product Mix (Assets)-OUTSIDE.1                                    2899
Product Mix (Assets)-PB ASSET.1                                    104
Product Mix (Assets)-THIRD PARTY.1                                3384
Product Penetration-H/UHNW MHHs with 4+ Products (%)              1851
Product Penetration-H/UHNW MHHs with 4+ Products (#)                81
Client Acquisition Details-QNR 2015                                594
Client Acquisition Details-QNR 2016                                585
Client Acquisition Details-QNR 2017                                633
Client Acquisition Details-3 year cumm. QNR                       1265
Client Acquisition Details-$1M+ QNR's 2015                         312
Client Acquisition Details-$1M+ QNR's 2016                         334
Client Acquisition Details-$1M+ QNR's 2017                         365
Client Acquisition Details-3 year cumm. $1m+ QNR                   686
Client Acquisition Details-YTD $1m+ QNRs (March-2018)              146
Net New Money Details-FA Comp. NNM 2014                           4147
Net New Money Details-FA Comp. NNM 2015                           4156
Net New Money Details-FA Comp. NNM 2016                           4163
Net New Money Details-FA Comp. NNM 2017                           4178
Net New Money Details-Magnitude of 2017 Growth                    4177
Net New Money Details-# of NNM Years +                               5
Net New Money Details-Rate of Increase / decrease                    6
Net New Money Details-2017 Break in NNM                              1
Net New Money Details-Color                                          4
Net New Money Details-Identifier                                    30
Net New Money Details-YTD NNM \n(March-2018)                      4138
Maketing Details-FA Web                                              2
Maketing Details-Page Views                                        327
Maketing Details-FA Social                                           2
Maketing Details-News Feed Activity                                 86
Maketing Details-Total AMC orders (12 month)                       283
Wealth/Financial Planning -Total MHHs                             2845
Wealth/Financial Planning -MHHs with a Plan                        265
Wealth/Financial Planning -Total MHH Planning %                   2260
Wealth/Financial Planning -H/UHNW MHH Planning %                  2383
Client Retention-% of Attrited MHHs (2017 - Post Split)           4070
Digital Tools-% of MHHs on OLS                                    3057
-% of MHHs on MTP                                                 1958
-# accts enrolled in MTP                                           363
-Outside Assets in MTP                                            2804
Banking and Lending-Cr Cards: H/UHNW MHHs Active CCs (%)          2192
Banking and Lending-Cr Cards: H/UHNW MHHs Active CCs ( #)           77
Banking and Lending-Mortgages: H/UHNW MHHs w/ Rocket Jumbo (%)    1365
Banking and Lending-Mortgages: H/UHNW MHHs w/ Rocket Jumbo (#)      45
Banking and Lending-SBL: H/UHNW MHHs Approved CLs (%)             2833
Banking and Lending-SBL: H/UHNW MHHs Approved CLs (#)              159
Banking and Lending-SBL: H/UHNW MHHs Utilized CLs (%)             2268
Banking and Lending-SBL: H/UHNW MHHs Utilized CLs (#)               75
Client Age Distribution (% of MHHs by Age)-<45 yrs.               1480
Client Age Distribution (% of MHHs by Age)-45-54 yrs.             1596
Client Age Distribution (% of MHHs by Age)-55-64 yrs.             1600
Client Age Distribution (% of MHHs by Age)-65-74 yrs.             1671
Client Age Distribution (% of MHHs by Age)-75+ yrs.               2002
Segment Index Score (All FA Score)-Advisory Score                   11
Segment Index Score (All FA Score)-Client Retention                 11
Segment Index Score (All FA Score)-Marketing Score                  41
Segment Index Score (All FA Score)-Planning Score                   38
Segment Index Score (All FA Score)-Client Acquisition Score         20
Segment Index Score (All FA Score)-Banking Score                    41
Segment Index Score (All FA Score)-Delivering the Firm              11
dtype: int64

# Constant feature
constant_features = feature_count[feature_count == 1].index.tolist()
constant_features

['Net New Money Details-2017 Break in NNM']

# Dropping constant feature

data.drop(constant_features, axis=1, inplace=True)

EDA (exploratory data analysis)¶

Target variable here is "Net New Money Details-YTD NNM \n(March-2018)"¶

# Defining target variable + data cleaning

data['target']   = data["Net New Money Details-YTD NNM \n(March-2018)"]

# Creating new columns in data for easy reusability + data cleaning

data['NNM2014'] =data['Net New Money Details-FA Comp. NNM 2014']
data['NNM2015'] =data['Net New Money Details-FA Comp. NNM 2015']
data['NNM2016'] =data['Net New Money Details-FA Comp. NNM 2016']
data['NNM2017'] =data['Net New Money Details-FA Comp. NNM 2017']

Analysis of trends in NNM Year by Year¶

# Comparing the NNM of 2014 with the NNM 2015

sns.jointplot(x="NNM2014", y="NNM2015", data =data ,  kind="reg" )
plt.show()

# Comparing the NNM of 2015 with the NNM 2016

sns.jointplot(x="NNM2015", y="NNM2016", data =data ,  kind="reg" )
plt.show()

# Comparing the NNM of 2016 with the NNM 2017

sns.jointplot(x="NNM2016", y="NNM2017", data =data ,  kind="reg" )
plt.show()

# Comparing the NNM of 2017 with the NNM 2018 YTD(year to date)

sns.jointplot(x="NNM2017", y="target", data =data ,  kind="reg" )
plt.show()

# sum af all previous NNM
data['NNM_sum'] = data[ ['NNM2014' ,'NNM2015' , 'NNM2016' ,'NNM2017']].sum(axis = 1)

# checking the infuence of NNM_sum with target
sns.jointplot(x="NNM_sum", y="target", data =data ,  kind="reg" )
plt.show()

# Skewness of distribution

#Skewnss gives the shape of normal distribution of each column. 
#Generally, if skewness value lies above +1 or below -1, the distribution is considered skewed.

def skewness(data):
    
    # The result is shown as a table defined as none_df for clarity. 
    skewness_df = data.skew().reset_index()

    # Define column labels for zero_df
    skewness_df.columns = ['column name', 'skewness %']

    # Display the columns in descending order of the percentage of zero values
    return skewness_df.sort_values('skewness %', ascending=False).reset_index(drop=True)

skewness(data.iloc[:,73:83])

From the joint plots, we can there are some extreme values at the edge of the distribution, indicating these are rare events. Generally these values will be problematic for linear models as they are sensitive to outliers.¶

num_cols = data.select_dtypes(exclude=['object'])

# plot
f, axes = plt.subplots(1, 2, figsize=(14, 6))

sns.boxplot(x='Net New Money Details-Color', y='target', 
            data=data, palette='husl', ax=axes[0])
sns.boxplot(x='Net New Money Details-Identifier', y='target', 
            data=data, palette='husl', ax=axes[1])
plt.xticks(rotation=90);

There are two categorical features in NNM and they seem to be related based on the column names. To confirm this, we will look at the variance inflation factor (VIF) score of these features¶

df_color = pd.concat([data['Net New Money Details-Color'], data['Net New Money Details-Identifier']], axis=1)

df_color = pd.get_dummies(df_color, drop_first=True)
df_color

from statsmodels.stats.outliers_influence import variance_inflation_factor

vif = pd.DataFrame()
vif["VIF Factor"] = [variance_inflation_factor(df_color.values, i) for i in range(df_color.shape[1])]
vif["features"] = df_color.columns

vif

# The VIF scores indicates that these two features tell the same inforamtion, so we will remove one of them.
data = data.drop('Net New Money Details-Identifier', axis=1)

# Dropping the ID column as we will not use it for prediction
data = data.drop('FA Details-FAID', axis=1)

# Number of unique entries in the categorical columns
data.select_dtypes(include=['object']).nunique()

FA Details-I-LOS Band                  9
FA Details-Rocket-LOS Band             9
FA Details-Sig Team.                 940
FA Details-Teaming Flag                2
FA Details-Rec. Council 2018           7
FA Details-Rocket Lifecycle Stage      9
FA Details-Production Quintile         5
FA Details-AuM YE Band                 9
Branch Detail-Branch Wire            289
Branch Detail-Division                 4
Net New Money Details-Color            4
Maketing Details-FA Web                2
Maketing Details-FA Social             2
dtype: int64

Analysis of FA details (features) influence on the target¶

# Comparing the FA I LOS with the target
sns.jointplot(x="FA Details-I-LOS", y="target", data =data ,  kind="reg" )
plt.show()

Observation: -ve correaltion shows --> with increase in Industry length of service(I LOS) NNM decreases¶

# Seeing the different bands and their counts
data['FA Details-I-LOS Band'].value_counts()

30-35       763
15-20       692
20-25       645
25-30       514
10-15       499
5-10        422
35-40       365
40 yrs.+    224
< 5 yrs.     62
Name: FA Details-I-LOS Band, dtype: int64

#Grouping by Industry Length of Service and ploting the means of different NNM years

plt.figure( figsize = (16,6))
plt.plot(data.groupby('FA Details-I-LOS Band')['target'].mean() , label = '2018')
plt.plot(data.groupby('FA Details-I-LOS Band')['NNM2014'].mean() ,  label = '2014')
plt.plot(data.groupby('FA Details-I-LOS Band')['NNM2015'].mean() ,  label = '2015')
plt.plot(data.groupby('FA Details-I-LOS Band')['NNM2016'].mean() ,  label = '2016')
plt.plot(data.groupby('FA Details-I-LOS Band')['NNM2017'].mean() ,  label = '2017')
plt.title('Mean NNM for different FA Details-IMean -LOS Band')
plt.legend()

<matplotlib.legend.Legend at 0x7f2be3631eb8>

#sorting results of 2017 year
data.groupby('FA Details-I-LOS Band')['NNM2017'].mean().sort_values()

FA Details-I-LOS Band
40 yrs.+   -4.041391e+06
35-40      -1.265476e+06
25-30      -5.012070e+05
30-35      -4.028784e+05
20-25       2.417895e+06
5-10        2.455194e+06
15-20       2.702601e+06
< 5 yrs.    2.905909e+06
10-15       3.338870e+06
Name: NNM2017, dtype: float64

Observation:
1) FA with FA Details-I-LOS Band = 10-15 years show best results
2) FA in 40 years I-LOS Band have the worst result

#Grouping by Rocket(particular to the bank) Length of Service and ploting the means of different NNM years

plt.figure( figsize = (16,6))
plt.plot(data.groupby('FA Details-Rocket-LOS Band')['target'].mean() , label = '2018')
plt.plot(data.groupby('FA Details-Rocket-LOS Band')['NNM2014'].mean() ,  label = '2014')
plt.plot(data.groupby('FA Details-Rocket-LOS Band')['NNM2015'].mean() ,  label = '2015')
plt.plot(data.groupby('FA Details-Rocket-LOS Band')['NNM2016'].mean() ,  label = '2016')
plt.plot(data.groupby('FA Details-Rocket-LOS Band')['NNM2017'].mean() ,  label = '2017')
plt.legend()

<matplotlib.legend.Legend at 0x7f2bdc307710>

Observation: FA shows decreasing NNM trend with increase in length of service¶

# FA Rocket lifecycle category counts

data['FA Details-Rocket Lifecycle Stage'].value_counts()

Recruits with >1 to <5 Yrs Left on EFL                    1943
Recruits with <1 Yr Left on EFL to <1 Yr Past EFL Exp.    1207
Recruits >1 Yr to <5 Yrs Past EFL Expiration               241
Recruits >=5 Yrs Left on EFL                               218
Recruits >=5 Yrs Past EFL Expiration                       198
Home Grown FA <10 Yrs Rocket LOS                           182
Home Grown FA >=10 Yrs Rocket LOS                          155
FA >=35 Yrs Industry LOS                                    29
TFAP                                                        13
Name: FA Details-Rocket Lifecycle Stage, dtype: int64

# FA Rocket lifecycle category grouped for mean calculation of target variable
data.groupby(['FA Details-Rocket Lifecycle Stage'])['target'].mean().sort_values()

FA Details-Rocket Lifecycle Stage
FA >=35 Yrs Industry LOS                                 -7.329262e+05
TFAP                                                     -6.024746e+05
Home Grown FA >=10 Yrs Rocket LOS                        -3.404159e+02
Recruits >1 Yr to <5 Yrs Past EFL Expiration              5.267928e+05
Recruits with <1 Yr Left on EFL to <1 Yr Past EFL Exp.    8.166083e+05
Recruits >=5 Yrs Past EFL Expiration                      8.776598e+05
Home Grown FA <10 Yrs Rocket LOS                          8.959585e+05
Recruits with >1 to <5 Yrs Left on EFL                    9.014280e+05
Recruits >=5 Yrs Left on EFL                              1.310788e+06
Name: target, dtype: float64

# Visualisation of the result from above cell

plt.figure( figsize = (16,6))
data.groupby(['FA Details-Rocket Lifecycle Stage'])['target'].mean().sort_values().plot.bar()
plt.show()

# FA Assests under Management(Year End) category counts

data['FA Details-AuM YE Band'].value_counts()

$150m-$200m    644
$75m-$100m     507
$100m-$125m    488
$50m-75m       479
$125m-$150m    456
>$350m         416
<$50m          401
$200m-$250m    399
$250m-$350m    396
Name: FA Details-AuM YE Band, dtype: int64

# Visualisation of grouping by - AuM YE Band to the means of the NNM values

plt.figure( figsize = (16,6))
plt.plot(data.groupby('FA Details-AuM YE Band')['target'].mean() , label = '2018')
plt.plot(data.groupby('FA Details-AuM YE Band')['NNM2015'].mean() ,  label = '2015')
plt.plot(data.groupby('FA Details-AuM YE Band')['NNM2016'].mean() ,  label = '2016')
plt.plot(data.groupby('FA Details-AuM YE Band')['NNM2017'].mean() ,  label = '2017')
plt.legend()

<matplotlib.legend.Legend at 0x7f2bd76d9470>

Observation: Generally FA with assests of worth 150m-350m worth under them bring more money ¶

Analysis of Client acquisition details features influence on the target¶

# Checking the "correlation" of the client acquisition details features with the target variable

client_acq_details_li = ['Client Acquisition Details-QNR 2015','Client Acquisition Details-QNR 2016',
                        'Client Acquisition Details-QNR 2017','Client Acquisition Details-3 year cumm. QNR',
                        "Client Acquisition Details-$1M+ QNR's 2015","Client Acquisition Details-$1M+ QNR's 2016",
                        "Client Acquisition Details-$1M+ QNR's 2017","Client Acquisition Details-YTD $1m+ QNRs (March-2018)"] 

for val in client_acq_details_li:
    sns.jointplot(x=val, y='target', data =data ,  kind="reg" )
    plt.show()

Observation: All the client acquisition variables show positive correaltion¶

Analysis of Marketing details of the FA¶

# Exploring the 'Maketing Details-FA Web' variable
data['Maketing Details-FA Web'].value_counts()

1.0    3797
0.0     389
Name: Maketing Details-FA Web, dtype: int64

# Grouping the Marketing details FA web and seeing the mean of target varialbe
data.groupby(['Maketing Details-FA Web'])['target'].mean().sort_values()

Maketing Details-FA Web
0.0   -112864.771979
1.0    922158.370192
Name: target, dtype: float64

# Graph representation of the above result
data.groupby(['Maketing Details-FA Web'])['target'].mean().sort_values().plot.bar()
plt.ylabel('target')
plt.show()

Observation: FA on FA web performer better¶

# Exploring the 'Maketing Details-FA Social' variable
data['Maketing Details-FA Social'].value_counts()

1.0    3014
0.0    1172
Name: Maketing Details-FA Social, dtype: int64

# Visualizing the marketing details FA social variable effect on target variable
data.groupby(['Maketing Details-FA Social'])['target'].mean().sort_values().plot.bar()
plt.ylabel('target')
plt.show()

# Visualizing the FA social data 

data.groupby(['Maketing Details-FA Social'])['target'].mean().sort_values().plot.bar()
plt.ylabel('target')
plt.show()

Observation: FA not on social media perform better¶

Segment Index Score (All FA Score)¶

# Plotting of the grouped mean by different Segment Index score for target

seg_index_li =['Segment Index Score (All FA Score)-Advisory Score','Segment Index Score (All FA Score)-Client Retention',
              'Segment Index Score (All FA Score)-Marketing Score','Segment Index Score (All FA Score)-Planning Score',
              'Segment Index Score (All FA Score)-Client Acquisition Score','Segment Index Score (All FA Score)-Banking Score',
              'Segment Index Score (All FA Score)-Delivering the Firm']
for val in seg_index_li:
    plt.plot(data.groupby(val)['target'].mean())
    plt.title(val)
    plt.show()

Observation: All features in the segment index score show positive correlation¶

# Deleting the shortcut names as they will again appear in the correlation matrix
data = data.drop(['NNM2014','NNM2015','NNM2016','NNM2017','NNM_sum','target'], axis=1)

There are two features 'FA Details-Sig Team.' and 'Branch Detail-Branch Wire' that have many categories. As we will need to convert the categorical variables to numeric using e.g. one-hot encoding in order to feed those features to any machine learning models, the conversion of the above two features will result in formation of many new columns, which can result in the curse of dimensionality. So we have to deal with these two features.¶

print('Number of categories in FA Details-Sig Team:\n', data['FA Details-Sig Team.'].nunique())

print('Number of values in top categories:\n', data['FA Details-Sig Team.'].value_counts().head())

Number of categories in FA Details-Sig Team:
 940
Number of values in top categories:
 Not Given    2062
T3UH            9
T3DN            9
T3J2            9
T341            7
Name: FA Details-Sig Team., dtype: int64

#As we can see that 'FA Details-Sig Team.' is very imbalanced, 
#which will give little information on the target variable, 
#we will remove this feature.
data = data.drop(['FA Details-Sig Team.'], axis=1)

# For 'Branch Detail-Branch Wire', we can group levels based on their frequency 
# to remove the number of levels while maintaining some information from this feature.

value_counts = data['Branch Detail-Branch Wire'].value_counts() # Specific column 
value_counts

PW           76
WL           71
WE           55
HK           53
AX           52
WI           50
TX           48
WS           47
TP           46
TS           46
JG           42
TZ           41
PJ           41
KU           40
PE           39
MW           39
UJ           37
DE           34
AJ           33
UN           33
FG           31
UG           31
WP           31
CE           29
CG           29
UX           29
LL           29
NI           28
BA           28
DK           28
MH           27
NE           27
BX           27
MV           27
JP           27
DS           26
KX           26
RP           26
AB           24
VQ           24
D2           24
C4           24
MR           24
XV           24
NS           24
JC           23
UT           23
CP           23
JW           23
TF           22
MQ           22
EY           22
GE           22
HB           22
VR           22
FD           22
WF           22
MN           22
NR           21
PY           21
RH           21
RA           21
JU           21
DT           21
7N           21
KA           21
J9           20
SS           20
VS           20
FC           20
RU           20
HG           20
CU           19
VN           19
H7           19
NG           19
SP           19
IP           19
CC           19
PT           19
FW           19
DL           19
RR           19
SU           18
LH           18
VP           18
KW           18
ML           18
VZ           18
KV           17
LO           17
FN           17
SR           17
DG           17
SO           17
TM           17
RF           17
UB           17
SJ           17
LV           17
EO           17
TN           16
YK           16
9N           16
BW           16
GS           16
PL           16
HI           16
BL           16
EM           16
FY           15
RW           15
HY           15
WD           15
7A           15
5G           15
FK           15
V6           15
GO           14
AS           14
XD           14
FF           14
LD           14
7C           14
TU           14
BY           14
LW           14
LB           14
XJ           14
NV           13
DW           13
VX           13
TJ           13
HW           13
BQ           13
JT           13
JK           13
GA           12
CF           12
RE           12
NU           12
ES           12
AI           12
KP           12
JR           12
UE           12
6Z           12
Not Given    12
HQ           11
CO           11
7T           11
CV           11
HA           11
XN           11
WX           11
RL           11
UH           11
FA           11
EX           11
VU           11
UC           10
CM           10
BB           10
KF           10
EH           10
AP           10
BT           10
HZ           10
WK           10
LC           10
WH           10
AT           10
8W            9
L2            9
US            9
FP            9
PA            9
B6            9
LZ            9
EI            9
8R            9
RC            9
HS            9
WW            8
8G            8
HU            8
BG            8
AR            8
UQ            8
HJ            8
8B            8
XM            7
KR            7
AD            7
WO            7
F6            7
JA            7
L8            7
R9            7
KJ            7
VI            7
MX            7
CZ            7
CW            7
KK            6
FE            6
A8            6
YE            6
OJ            6
AH            6
KB            6
TV            6
OA            6
WG            6
HH            6
GH            6
8E            6
WA            6
KS            6
NZ            6
FJ            6
F5            6
BU            6
RQ            5
9D            5
KN            5
BP            5
OY            5
SM            5
KQ            5
8X            5
UF            5
UZ            5
KY            5
SA            5
MT            5
A7            5
S9            5
7S            5
SQ            5
JH            4
WT            4
8T            4
FR            4
PP            4
JY            4
6N            4
EA            4
1W            4
XL            4
GL            4
SC            4
7E            4
8D            4
CD            3
WV            3
EB            3
9G            3
UW            3
UL            3
6T            3
DC            3
K7            3
OB            3
6P            3
BK            3
GR            3
RT            3
WQ            3
LS            2
PH            2
G7            2
MG            2
ST            2
HM            2
EP            2
NB            2
6R            2
LN            2
9X            1
8U            1
6S            1
HT            1
JD            1
M4            1
OV            1
RG            1
8O            1
F4            1
Name: Branch Detail-Branch Wire, dtype: int64

count_10 = value_counts[value_counts <= 10].index
count_betwen_10_20 = value_counts[(value_counts > 10) & (value_counts <=20)].index
count_betwen_20_30 = value_counts[(value_counts > 20) & (value_counts <=30)].index
count_betwen_30_40 = value_counts[(value_counts > 30) & (value_counts <=40)].index
count_betwen_40_50 = value_counts[(value_counts > 40) & (value_counts <=50)].index
count_betwen_50_60 = value_counts[(value_counts > 50) & (value_counts <=60)].index
count_60 = value_counts[value_counts > 60].index

data['Branch Detail-Branch Wire'] = data['Branch Detail-Branch Wire'].replace(count_10, '<=10')
data['Branch Detail-Branch Wire'] = data['Branch Detail-Branch Wire'].replace(count_betwen_10_20, '>10&<=20')
data['Branch Detail-Branch Wire'] = data['Branch Detail-Branch Wire'].replace(count_betwen_20_30, '>20&<=30')
data['Branch Detail-Branch Wire'] = data['Branch Detail-Branch Wire'].replace(count_betwen_30_40, '>30&<=40')
data['Branch Detail-Branch Wire'] = data['Branch Detail-Branch Wire'].replace(count_betwen_40_50, '>40&<=50')
data['Branch Detail-Branch Wire'] = data['Branch Detail-Branch Wire'].replace(count_betwen_50_60, '>50&<=60')
data['Branch Detail-Branch Wire'] = data['Branch Detail-Branch Wire'].replace(count_60, '>60')

data['Branch Detail-Branch Wire'].value_counts()

>10&<=20    1427
>20&<=30    1039
<=10         704
>40&<=50     361
>30&<=40     348
>50&<=60     160
>60          147
Name: Branch Detail-Branch Wire, dtype: int64

Correlation of numerical features with the target¶

To determine if a variable is useful, we can calculate the Pearson Correlation Coefficient between this variable and the target. This measures the strength of a linear relationship between the input feature and the target. The resulting value lies in [-1;1], with -1 meaning perfect negative correlation (as one variable increases, the other decreases), +1 meaning perfect positive correlation and 0 meaning no linear correlation between the two variables.

# Plot the correlation matrix

k = 15 # number of variables for heatmap
corrmat = data.corr(method='pearson') # creates a complete correlation matrix
cols = corrmat.nlargest(15, 'Net New Money Details-YTD NNM \n(March-2018)')['Net New Money Details-YTD NNM \n(March-2018)'].index
# In the above step, gets only the highest 15 correlated columns
a = pd.DataFrame(data[cols].values)
cm = a.corr()
sns.set(font_scale=1.25)
f, ax = plt.subplots(figsize=(12, 9))
hm = sns.heatmap(cm, cbar=True, annot=True, square=True, fmt='.2f', annot_kws={'size': 10}, yticklabels=cols.values, xticklabels=cols.values)
plt.show()

# Compute the correlations with the target and sort
correlations = data.corr()['Net New Money Details-YTD NNM \n(March-2018)'].sort_values(ascending=False)

# Display postive correlations
print('Correlations with target variable:\n\n', correlations.head(10))

Correlations with target variable:

 Net New Money Details-YTD NNM \n(March-2018)             1.000000
Client Acquisition Details-YTD $1m+ QNRs (March-2018)    0.303240
Client Acquisition Details-3 year cumm. $1m+ QNR         0.223786
Net New Money Details-FA Comp. NNM 2015                  0.210939
Client Acquisition Details-3 year cumm. QNR              0.208898
Product Mix (Assets)-Wrap                                0.201267
Client Acquisition Details-$1M+ QNR's 2017               0.197701
Client Acquisition Details-QNR 2017                      0.197636
Net New Money Details-# of NNM Years +                   0.196321
Net New Money Details-FA Comp. NNM 2017                  0.195823
Name: Net New Money Details-YTD NNM \n(March-2018), dtype: float64

# Display negative correlations
print('Correlations with target variable:\n\n', correlations.tail(10))

Correlations with target variable:

 Product Mix (Rev)-TAXABLE FIXED I.1              -0.049181
FA Details-Rocket-LOS                            -0.054634
Product Mix (Rev)-MUNICIPALS.1                   -0.054732
Product Mix (Rev)-MUNICIPALS                     -0.059756
Product Mix (Assets)-EQUITY                      -0.061268
Product Mix (Assets)-CASH .1                     -0.071224
Product Mix (Rev)-EQUITIES.1                     -0.076679
FA Details-I-LOS                                 -0.086027
Product Mix (Rev)-EQUITIES                       -0.093693
Net New Money Details-Magnitude of 2017 Growth   -0.116826
Name: Net New Money Details-YTD NNM \n(March-2018), dtype: float64

Correlation of categorical features with the target¶

# find categorical features
cat_cols = data.select_dtypes(include=['object'])

# One-hot encoding categorical feature"
cat_cols_enc = pd.get_dummies(cat_cols, drop_first=True)

# Combine with target variable
cat_cols_enc_y = pd.concat([cat_cols_enc, data['Net New Money Details-YTD NNM \n(March-2018)']], axis=1)

# Plot the correlation matrix

k = 15 # number of variables for heatmap
corrmat = cat_cols_enc_y.corr(method='pearson')
cols = corrmat.nlargest(15, 'Net New Money Details-YTD NNM \n(March-2018)')['Net New Money Details-YTD NNM \n(March-2018)'].index
a = pd.DataFrame(cat_cols_enc_y[cols].values)
cm = a.corr()
sns.set(font_scale=1.25)
f, ax = plt.subplots(figsize=(12, 9))
hm = sns.heatmap(cm, cbar=True, annot=True, square=True, fmt='.2f', annot_kws={'size': 10}, yticklabels=cols.values, xticklabels=cols.values)
plt.show()

Observation: We observe weak correlation of these categorical variables with the target.This captures only linear dependency of each feature with the target. We will look at other model-based methods to evaluate feature importance later on.¶

Deal with collinear features¶

Collinear features are features that are highly correlated with one another.For each pair of correlated features, we only need one.

# Combine numeric and encoded categorical features
df_new = pd.concat([num_cols, cat_cols_enc], axis=1)

# Compute correlation matrix
corr_matrix = df_new.corr().abs()

# The matrix is symmetric so we need to extract upper triangle matrix without diagonal (k = 1)
corr_pairs = (corr_matrix.where(np.triu(np.ones(corr_matrix.shape), k=1).astype(np.bool))
                 .stack()
                 .sort_values(ascending=False))

corr_pairs.head(50)

Product Mix (Assets)-Wrap %                                     Segment Index Score (All FA Score)-Advisory Score                 0.984924
Product Mix (Rev)-INSURANCE / ANNUITIES                         Product Mix (Rev)-OTHER                                           0.956326
Product Penetration-H/UHNW MHHs with 4+ Products (%)            Segment Index Score (All FA Score)-Delivering the Firm            0.951036
Wealth/Financial Planning -H/UHNW MHH Planning %                Segment Index Score (All FA Score)-Planning Score                 0.939326
Product Mix (Rev)-TAXABLE FIXED I.1                             Product Mix (Assets)-FIXED INCOME.1                               0.937472
Product Mix (Rev)-ADVISORY                                      Product Mix (Assets)-Wrap                                         0.933322
Wealth/Financial Planning -Total MHH Planning %                 Wealth/Financial Planning -H/UHNW MHH Planning %                  0.909926
Product Mix (Assets)-CASH .1                                    Product Mix (Assets)-FIXED INCOME.1                               0.883437
Banking and Lending-SBL: H/UHNW MHHs Approved CLs (#)           Banking and Lending-SBL: H/UHNW MHHs Utilized CLs (#)             0.869566
Client Acquisition Details-3 year cumm. QNR                     Client Acquisition Details-3 year cumm. $1m+ QNR                  0.865056
Wealth/Financial Planning -Total MHH Planning %                 Segment Index Score (All FA Score)-Planning Score                 0.863389
Product Mix (Rev)-TAXABLE FIXED I.1                             Product Mix (Assets)-CASH .1                                      0.861450
Banking and Lending-Cr Cards: H/UHNW MHHs Active CCs ( #)       Banking and Lending-SBL: H/UHNW MHHs Approved CLs (#)             0.851861
Client Acquisition Details-QNR 2017                             Client Acquisition Details-$1M+ QNR's 2017                        0.847478
Client Acquisition Details-QNR 2016                             Client Acquisition Details-3 year cumm. QNR                       0.843422
Client Acquisition Details-QNR 2017                             Client Acquisition Details-3 year cumm. QNR                       0.840486
FA Details-T12\n(Feb 2018)                                      Product Mix (Rev)-ADVISORY                                        0.837502
Client Acquisition Details-QNR 2015                             Client Acquisition Details-3 year cumm. QNR                       0.837098
Product Mix (Rev)-Alts..1                                       Product Mix (Assets)-NON-TRADITIONAL.1                            0.818712
Client Acquisition Details-$1M+ QNR's 2017                      Client Acquisition Details-3 year cumm. $1m+ QNR                  0.818565
Client Acquisition Details-QNR 2016                             Client Acquisition Details-$1M+ QNR's 2016                        0.812042
FA Details-AuM YE 17                                            Product Mix (Assets)-FIXED INCOME.1                               0.808361
FA Details-T12\n(Feb 2018)                                      Product Mix (Assets)-Wrap                                         0.797742
Client Acquisition Details-$1M+ QNR's 2016                      Client Acquisition Details-3 year cumm. $1m+ QNR                  0.792829
Client Acquisition Details-3 year cumm. QNR                     Segment Index Score (All FA Score)-Client Acquisition Score       0.787219
FA Details-Rocket-LOS Band_< 5 yrs.                             FA Details-Rocket Lifecycle Stage_Recruits >=5 Yrs Left on EFL    0.786953
FA Details-AuM YE 17                                            Product Mix (Assets)-CASH .1                                      0.782375
Product Mix (Rev)-STRUCTURED PROD                               Product Mix (Rev)-STRUCTURED PROD.1                               0.781855
Banking and Lending-Cr Cards: H/UHNW MHHs Active CCs ( #)       Banking and Lending-SBL: H/UHNW MHHs Utilized CLs (#)             0.777918
Client Acquisition Details-QNR 2015                             Client Acquisition Details-$1M+ QNR's 2015                        0.766964
Client Acquisition Details-3 year cumm. $1m+ QNR                Segment Index Score (All FA Score)-Client Acquisition Score       0.762967
Client Acquisition Details-$1M+ QNR's 2015                      Client Acquisition Details-3 year cumm. $1m+ QNR                  0.760870
Client Acquisition Details-QNR 2017                             Client Acquisition Details-3 year cumm. $1m+ QNR                  0.756196
Product Mix (Rev)-LENDING                                       Product Mix (Rev)-LENDING.1                                       0.748600
Product Mix (Assets)-PB ASSET                                   Product Mix (Assets)-PB ASSET.1                                   0.735327
Client Acquisition Details-QNR 2016                             Client Acquisition Details-3 year cumm. $1m+ QNR                  0.722652
FA Details-AuM YE 17                                            Product Mix (Rev)-TAXABLE FIXED I.1                               0.710304
Banking and Lending-Mortgages: H/UHNW MHHs w/ Rocket Jumbo (#)  Banking and Lending-SBL: H/UHNW MHHs Approved CLs (#)             0.703947
                                                                Banking and Lending-SBL: H/UHNW MHHs Utilized CLs (#)             0.703931
Client Acquisition Details-QNR 2015                             Client Acquisition Details-3 year cumm. $1m+ QNR                  0.699042
Client Acquisition Details-3 year cumm. QNR                     Client Acquisition Details-$1M+ QNR's 2017                        0.698589
                                                                Client Acquisition Details-$1M+ QNR's 2016                        0.698274
Product Mix (Rev)-MUNICIPALS                                    Product Mix (Rev)-MUNICIPALS.1                                    0.693819
Product Mix (Assets)-THIRD PARTY                                Product Mix (Assets)-THIRD PARTY.1                                0.689606
Maketing Details-News Feed Activity                             Segment Index Score (All FA Score)-Marketing Score                0.685203
Product Mix (Assets)-OUTSIDE                                    Product Mix (Assets)-OUTSIDE.1                                    0.681930
Client Acquisition Details-QNR 2015                             Segment Index Score (All FA Score)-Client Acquisition Score       0.668380
Client Retention-% of Attrited MHHs (2017 - Post Split)         Segment Index Score (All FA Score)-Client Retention               0.668119
Client Acquisition Details-QNR 2016                             Segment Index Score (All FA Score)-Client Acquisition Score       0.666980
FA Details-T12\n(Feb 2018)                                      FA Details-Rec. Council 2018_Not Given                            0.666083
dtype: float64

# Remove one from each correlated pair with correlation coeffficients > 0.9

df_new = df_new.drop(['Segment Index Score (All FA Score)-Advisory Score', 
                      'Product Mix (Rev)-OTHER', 
                      'Segment Index Score (All FA Score)-Delivering the Firm',
                      'Segment Index Score (All FA Score)-Planning Score',
                      'Product Mix (Assets)-FIXED INCOME.1',
                      'Product Mix (Assets)-Wrap',
                      'Wealth/Financial Planning -H/UHNW MHH Planning %'], axis=1)

df_new.to_csv("Processed_features.csv")

	FA Details-FAID	FA Details-I-LOS	FA Details-Rocket-LOS	FA Details-I-LOS Band	FA Details-Rocket-LOS Band	FA Details-Sig Team.	FA Details-Teaming Flag	FA Details-Rec. Council 2018	FA Details-Rocket Lifecycle Stage	FA Details-Yrs. Left on EFL (Feb 2018)	FA Details-Production Quintile	FA Details-T12 (Feb 2018)	FA Details-AuM YE 17	FA Details-AuM YE Band	Branch Detail-Branch Wire	Branch Detail-Division	Product Mix (Rev)-Advisory %	Product Mix (Rev)-Alts.	Product Mix (Rev)-BANKING	Product Mix (Rev)-CREDIT LINE	Product Mix (Rev)-EQUITIES	Product Mix (Rev)-INSURANCE / ANNUITIES	Product Mix (Rev)-LENDING	Product Mix (Rev)-MISCELLANEOUS	Product Mix (Rev)-MUNICIPALS	Product Mix (Rev)-MF and UIT	Product Mix (Rev)-OTHER	Product Mix (Rev)-STRUCTURED PROD	Product Mix (Rev)-TAXABLE FIXED I	Product Mix (Rev)-ADVISORY	Product Mix (Rev)-Alts..1	Product Mix (Rev)-BANKING.1	Product Mix (Rev)-CREDIT LINE.1	Product Mix (Rev)-EQUITIES.1	Product Mix (Rev)-INSURANCE / ANNUITIES.1	Product Mix (Rev)-LENDING.1	Product Mix (Rev)-MISCELLANEOUS.1	Product Mix (Rev)-MUNICIPALS.1	Product Mix (Rev)-MF and UIT.1	Product Mix (Rev)-OTHER.1	Product Mix (Rev)-STRUCTURED PROD.1	Product Mix (Rev)-TAXABLE FIXED I.1	Product Mix (Assets)-Wrap %	Product Mix (Assets)-CASH	Product Mix (Assets)-COMMODITIES	Product Mix (Assets)-EQUITY	Product Mix (Assets)-FIXED INCOME	Product Mix (Assets)-NON-TRADITIONAL	Product Mix (Assets)-OTHER	Product Mix (Assets)-OUTSIDE	Product Mix (Assets)-THIRD PARTY	Product Mix (Assets)-Wrap	Product Mix (Assets)-CASH .1	Product Mix (Assets)-COMMODITIES.1	Product Mix (Assets)-EQUITY.1	Product Mix (Assets)-FIXED INCOME.1	Product Mix (Assets)-NON-TRADITIONAL.1	Product Mix (Assets)-OTHER.1	Product Mix (Assets)-OUTSIDE.1	Product Mix (Assets)-THIRD PARTY.1	Product Penetration-H/UHNW MHHs with 4+ Products (%)	Product Penetration-H/UHNW MHHs with 4+ Products (#)	Client Acquisition Details-QNR 2015	Client Acquisition Details-QNR 2016	Client Acquisition Details-QNR 2017	Client Acquisition Details-3 year cumm. QNR	Client Acquisition Details-$1M+ QNR's 2015	Client Acquisition Details-$1M+ QNR's 2016	Client Acquisition Details-$1M+ QNR's 2017	Client Acquisition Details-3 year cumm. $1m+ QNR	Client Acquisition Details-YTD $1m+ QNRs (March-2018)	Net New Money Details-FA Comp. NNM 2014	Net New Money Details-FA Comp. NNM 2015	Net New Money Details-FA Comp. NNM 2016	Net New Money Details-FA Comp. NNM 2017	Net New Money Details-Magnitude of 2017 Growth	Net New Money Details-# of NNM Years +	Net New Money Details-Rate of Increase / decrease	Net New Money Details-2017 Break in NNM	Net New Money Details-Color	Net New Money Details-Identifier	Net New Money Details-YTD NNM (March-2018)	Maketing Details-FA Web	Maketing Details-Page Views	Maketing Details-FA Social	Maketing Details-News Feed Activity	Maketing Details-Total AMC orders (12 month)	Wealth/Financial Planning -Total MHHs	Wealth/Financial Planning -MHHs with a Plan	Wealth/Financial Planning -Total MHH Planning %	Wealth/Financial Planning -H/UHNW MHH Planning %	Client Retention-% of Attrited MHHs (2017 - Post Split)	Digital Tools-% of MHHs on OLS	-% of MHHs on MTP	-# accts enrolled in MTP	-Outside Assets in MTP	Banking and Lending-Cr Cards: H/UHNW MHHs Active CCs (%)	Banking and Lending-Cr Cards: H/UHNW MHHs Active CCs ( #)	Banking and Lending-Mortgages: H/UHNW MHHs w/ Rocket Jumbo (%)	Banking and Lending-Mortgages: H/UHNW MHHs w/ Rocket Jumbo (#)	Banking and Lending-SBL: H/UHNW MHHs Approved CLs (%)	Banking and Lending-SBL: H/UHNW MHHs Approved CLs (#)	Banking and Lending-SBL: H/UHNW MHHs Utilized CLs (%)	Banking and Lending-SBL: H/UHNW MHHs Utilized CLs (#)	Client Age Distribution (% of MHHs by Age)-<45 yrs.	Client Age Distribution (% of MHHs by Age)-45-54 yrs.	Client Age Distribution (% of MHHs by Age)-55-64 yrs.	Client Age Distribution (% of MHHs by Age)-65-74 yrs.	Client Age Distribution (% of MHHs by Age)-75+ yrs.	Segment Index Score (All FA Score)-Advisory Score	Segment Index Score (All FA Score)-Client Retention	Segment Index Score (All FA Score)-Marketing Score	Segment Index Score (All FA Score)-Planning Score	Segment Index Score (All FA Score)-Client Acquisition Score	Segment Index Score (All FA Score)-Banking Score	Segment Index Score (All FA Score)-Delivering the Firm
0	1	12.05	9.04	10-15	5-10	NaN	0	DIRECTOR	Recruits with <1 Yr Left on EFL to <1 Yr Past ...	-0.2	1.0	1349015.30	1.509757e+08	$150m-$200m	AJ	ABCD CENTRAL	0.70205	0.00791392	0.0312993	0.0516782	0.00494227	0.0738508	0	0.000410861	0.0392895	0.0562817	0.0179356	0	0.0143478	244041	2750.97	10880	17963.9	1717.99	25671.4	0	142.82	13657.5	19564.2	6234.64	0	4987.45	0.68578	0.0373857	0.00703884	0.567252	0.229789	0.0102348	0.1483	2.60969e-07	0	1.03536e+08	5.64433e+06	1.06269e+06	8.56412e+07	3.46925e+07	1.54521e+06	2.23897e+07	39.4	0	0.025000	1	9	10	7.54	26.54	2	1	1	4.00	1	12616064	12324745	15141130	281474	-0.001873	4	-1	no action	Blue	B1	5.65313e+06	1	NaN	1	20	136	174.051	22	0.1264	0.249927	0.0423664	0.758794	0.040201	60	1720727	0.114200	4	0.200000	7	0.567511	21	0.378319	14	0.4523	0.1904	0.1666	0.1547	0.0357	9	7	6.00	5.25	9.0	4.00	1
1	2	8.08	8.11	5-10	5-10	T3WN	1	NaN	Home Grown FA <10 Yrs Rocket LOS	NaN	3.0	615707.13	7.922755e+07	$75m-$100m	AJ	ABCD CENTRAL	0.785594	0.00431089	0.0269257	0.0905445	0.0243019	0.013128	0.00116337	9.08157e-06	0.000124623	0.0357323	0.00644758	0.00593915	0.00577893	118511	650.32	4061.87	13659.1	3666.07	1980.43	175.5	1.37	18.8	5390.39	972.65	895.95	871.78	0.669363	0.0441211	0.000320762	0.575536	0.264506	0.00767018	0.0483356	0.0400602	0.0194494	5.3032e+07	3.49561e+06	25413.2	4.55983e+07	2.09562e+07	607690	3.82951e+06	3.17387e+06	1.54092e+06	0.045876	4	2	3	2	7.00	1	2	2	5.00	-	1481529	3333014	7127538	5710260	-0.082941	4	-1	no action	Blue	B1	5.2288e+06	1	109	1	22	23	211.095	43	0.2037	0.35	0.102619	0.8159	0.0627615	71	7756301	0.166600	1	0.000000	0	0.333300	2	0.166600	1	0.0641	0.1923	0.423	0.1923	0.1282	9	2	5.50	6.25	8.5	7.75	1
2	3	12.02	6.06	10-15	5-10	T3GE	1	NaN	Recruits with >1 to <5 Yrs Left on EFL	2.8	5.0	500575.60	5.665123e+07	$50m-75m	AJ	ABCD CENTRAL	0.67092	0.0275205	0.0196022	0.0434165	0.0247356	0.0677448	0	0.00391118	0.000206578	0.111155	-0.0268258	0.0444504	0.0131634	83597.7	3429.1	2442.47	5409.77	3082.1	8441.11	0	487.34	25.74	13850.1	-3342.54	5538.59	1640.18	0.518085	0.058935	0.00222697	0.371455	0.311938	0.0289556	0.147944	0.0229955	0.0555499	2.93502e+07	3.33874e+06	126161	2.10434e+07	1.76717e+07	1.64037e+06	8.3812e+06	1.30272e+06	3.14697e+06	0.130422	6	2	3.5	0.5	6.00		1	0.5	1.50	-	3770831	2872686	1952530	1840096	-0.031350	4	-3	no action	Blue	B3	158973	1	101	1	43	81	175	56	0.3200	0.521728	0.0937818	0.8125	0.0913462	187	25422185	0.070364	5	0.070392	5	0.309797	22	0.098580	7	0.114	0.1403	0.2894	0.2543	0.2017	7	2	7.75	8.25	5.0	6.00	3
3	4	16.05	8.11	15-20	5-10	T3DJ	1	CHAIRMAN	Recruits with <1 Yr Left on EFL to <1 Yr Past ...	0.2	1.0	2874005.48	2.946003e+08	$250m-$350m	AJ	ABCD CENTRAL	0.551391	0.00566654	0.0274874	0.258494	0.0724744	0.0187912	0.00229627	0.00427678	0	0.0536286	-6.28388e-05	0.00555622	0	408813	4201.29	20379.7	191653	53734	13932.2	1702.5	3170.89	0	39761.4	-46.59	4119.5	0	0.75226	0.174334	0.00164423	0.337523	0.35558	0.0508686	0.0648618	0.00706777	0.00812024	2.21616e+08	5.13587e+07	484392	9.94345e+07	1.04754e+08	1.49859e+07	1.91083e+07	2.08217e+06	2.39223e+06	0.302579	23	0.5	1	6.5	8.00	0.5	1	2.5	4.00	0.5	4719953	-4365132	12942012	1026925	-0.004007	3	-1	no action	Blue	B4	4.23083e+07	1	39	1	19	116	148.011	16	0.1081	0.202627	0.14144	0.882716	0.0740741	112	49014804	0.106767	11	0.043900	4	0.495114	51	0.242651	25	-	-	-	-	-	10	1	6.75	6	7.5	10.00	8
4	5	37.07	19.01	35-40	15-20	T3NF	1	PRESIDENT	Recruits with >1 to <5 Yrs Left on EFL	4.9	1.0	2015931.35	4.080550e+08	>$350m	AJ	ABCD CENTRAL	0.102297	0.0211128	0.0214352	0.110645	0.0941928	0.0530022	0	0.000444709	0.0276821	0.423894	0.0245316	0	0.120763	54526.7	11253.6	11425.5	58976.1	50206.9	28251.4	0	237.04	14755.2	225945	13075.9	0	64369.4	0.0693424	0.0375341	0.000474557	0.505691	0.278984	0.01328	0.135374	0.014328	0.0143347	2.82955e+07	1.5316e+07	193645	2.0635e+08	1.13841e+08	5.41897e+06	5.52399e+07	5.84663e+06	5.84936e+06	0.033538	4	3.25	6.5	3.04	12.79	0.65	2.58	1.2	4.43	-	6414463	-3319428	190608	-7646012	0.018169	2	-1	no action	Blue	B7	-692298	1	64	0	NaN	132	317.46	4	0.0126	0.0338489	0.0358365	0.644886	0.0198864	71	533121	0.191279	22	0.078238	9	0.347768	40	0.173878	20	0.1361	0.1191	0.2042	0.2638	0.2765	1	7	5.75	1	9.0	5.00	1

	FA Details-I-LOS Band	FA Details-Rocket-LOS Band	FA Details-Sig Team.	FA Details-Rec. Council 2018	FA Details-Rocket Lifecycle Stage	FA Details-AuM YE Band	Branch Detail-Branch Wire	Branch Detail-Division	Product Mix (Rev)-Advisory %	Product Mix (Rev)-Alts.	Product Mix (Rev)-BANKING	Product Mix (Rev)-CREDIT LINE	Product Mix (Rev)-EQUITIES	Product Mix (Rev)-INSURANCE / ANNUITIES	Product Mix (Rev)-LENDING	Product Mix (Rev)-MISCELLANEOUS	Product Mix (Rev)-MUNICIPALS	Product Mix (Rev)-MF and UIT	Product Mix (Rev)-OTHER	Product Mix (Rev)-STRUCTURED PROD	Product Mix (Rev)-TAXABLE FIXED I	Product Mix (Rev)-ADVISORY	Product Mix (Rev)-Alts..1	Product Mix (Rev)-BANKING.1	Product Mix (Rev)-CREDIT LINE.1	Product Mix (Rev)-EQUITIES.1	Product Mix (Rev)-INSURANCE / ANNUITIES.1	Product Mix (Rev)-LENDING.1	Product Mix (Rev)-MISCELLANEOUS.1	Product Mix (Rev)-MUNICIPALS.1	Product Mix (Rev)-MF and UIT.1	Product Mix (Rev)-OTHER.1	Product Mix (Rev)-STRUCTURED PROD.1	Product Mix (Rev)-TAXABLE FIXED I.1	Product Mix (Assets)-Wrap %	Product Mix (Assets)-CASH	Product Mix (Assets)-COMMODITIES	Product Mix (Assets)-EQUITY	Product Mix (Assets)-FIXED INCOME	Product Mix (Assets)-NON-TRADITIONAL	Product Mix (Assets)-OTHER	Product Mix (Assets)-OUTSIDE	Product Mix (Assets)-THIRD PARTY	Product Mix (Assets)-Wrap	Product Mix (Assets)-CASH .1	Product Mix (Assets)-COMMODITIES.1	Product Mix (Assets)-EQUITY.1	Product Mix (Assets)-FIXED INCOME.1	Product Mix (Assets)-NON-TRADITIONAL.1	Product Mix (Assets)-OTHER.1	Product Mix (Assets)-OUTSIDE.1	Product Mix (Assets)-THIRD PARTY.1	Client Acquisition Details-QNR 2015	Client Acquisition Details-QNR 2016	Client Acquisition Details-QNR 2017	Client Acquisition Details-$1M+ QNR's 2015	Client Acquisition Details-$1M+ QNR's 2016	Client Acquisition Details-$1M+ QNR's 2017	Client Acquisition Details-YTD $1m+ QNRs (March-2018)	Net New Money Details-FA Comp. NNM 2014	Net New Money Details-FA Comp. NNM 2015	Net New Money Details-FA Comp. NNM 2016	Net New Money Details-2017 Break in NNM	Net New Money Details-Color	Net New Money Details-Identifier	Net New Money Details-YTD NNM (March-2018)	Maketing Details-FA Web	Maketing Details-Page Views	Maketing Details-FA Social	Maketing Details-News Feed Activity	Maketing Details-Total AMC orders (12 month)	Wealth/Financial Planning -Total MHHs	Wealth/Financial Planning -H/UHNW MHH Planning %	Client Retention-% of Attrited MHHs (2017 - Post Split)	Digital Tools-% of MHHs on OLS	-% of MHHs on MTP	-# accts enrolled in MTP	-Outside Assets in MTP	Client Age Distribution (% of MHHs by Age)-<45 yrs.	Client Age Distribution (% of MHHs by Age)-45-54 yrs.	Client Age Distribution (% of MHHs by Age)-55-64 yrs.	Client Age Distribution (% of MHHs by Age)-65-74 yrs.	Client Age Distribution (% of MHHs by Age)-75+ yrs.	Segment Index Score (All FA Score)-Advisory Score	Segment Index Score (All FA Score)-Client Retention	Segment Index Score (All FA Score)-Planning Score
0	10-15	5-10	NaN	DIRECTOR	Recruits with <1 Yr Left on EFL to <1 Yr Past ...	$150m-$200m	AJ	ABCD CENTRAL	0.70205	0.00791392	0.0312993	0.0516782	0.00494227	0.0738508	0	0.000410861	0.0392895	0.0562817	0.0179356	0	0.0143478	244041	2750.97	10880	17963.9	1717.99	25671.4	0	142.82	13657.5	19564.2	6234.64	0	4987.45	0.68578	0.0373857	0.00703884	0.567252	0.229789	0.0102348	0.1483	2.60969e-07	0	1.03536e+08	5.64433e+06	1.06269e+06	8.56412e+07	3.46925e+07	1.54521e+06	2.23897e+07	39.4	0	9	10	7.54	2	1	1	1	12616064	12324745	15141130	no action	Blue	B1	5.65313e+06	1	NaN	1	20	136	174.051	0.249927	0.0423664	0.758794	0.040201	60	1720727	0.4523	0.1904	0.1666	0.1547	0.0357	9	7	5.25
1	5-10	5-10	T3WN	NaN	Home Grown FA <10 Yrs Rocket LOS	$75m-$100m	AJ	ABCD CENTRAL	0.785594	0.00431089	0.0269257	0.0905445	0.0243019	0.013128	0.00116337	9.08157e-06	0.000124623	0.0357323	0.00644758	0.00593915	0.00577893	118511	650.32	4061.87	13659.1	3666.07	1980.43	175.5	1.37	18.8	5390.39	972.65	895.95	871.78	0.669363	0.0441211	0.000320762	0.575536	0.264506	0.00767018	0.0483356	0.0400602	0.0194494	5.3032e+07	3.49561e+06	25413.2	4.55983e+07	2.09562e+07	607690	3.82951e+06	3.17387e+06	1.54092e+06	2	3	2	1	2	2	-	1481529	3333014	7127538	no action	Blue	B1	5.2288e+06	1	109	1	22	23	211.095	0.35	0.102619	0.8159	0.0627615	71	7756301	0.0641	0.1923	0.423	0.1923	0.1282	9	2	6.25
2	10-15	5-10	T3GE	NaN	Recruits with >1 to <5 Yrs Left on EFL	$50m-75m	AJ	ABCD CENTRAL	0.67092	0.0275205	0.0196022	0.0434165	0.0247356	0.0677448	0	0.00391118	0.000206578	0.111155	-0.0268258	0.0444504	0.0131634	83597.7	3429.1	2442.47	5409.77	3082.1	8441.11	0	487.34	25.74	13850.1	-3342.54	5538.59	1640.18	0.518085	0.058935	0.00222697	0.371455	0.311938	0.0289556	0.147944	0.0229955	0.0555499	2.93502e+07	3.33874e+06	126161	2.10434e+07	1.76717e+07	1.64037e+06	8.3812e+06	1.30272e+06	3.14697e+06	2	3.5	0.5		1	0.5	-	3770831	2872686	1952530	no action	Blue	B3	158973	1	101	1	43	81	175	0.521728	0.0937818	0.8125	0.0913462	187	25422185	0.114	0.1403	0.2894	0.2543	0.2017	7	2	8.25
3	15-20	5-10	T3DJ	CHAIRMAN	Recruits with <1 Yr Left on EFL to <1 Yr Past ...	$250m-$350m	AJ	ABCD CENTRAL	0.551391	0.00566654	0.0274874	0.258494	0.0724744	0.0187912	0.00229627	0.00427678	0	0.0536286	-6.28388e-05	0.00555622	0	408813	4201.29	20379.7	191653	53734	13932.2	1702.5	3170.89	0	39761.4	-46.59	4119.5	0	0.75226	0.174334	0.00164423	0.337523	0.35558	0.0508686	0.0648618	0.00706777	0.00812024	2.21616e+08	5.13587e+07	484392	9.94345e+07	1.04754e+08	1.49859e+07	1.91083e+07	2.08217e+06	2.39223e+06	0.5	1	6.5	0.5	1	2.5	0.5	4719953	-4365132	12942012	no action	Blue	B4	4.23083e+07	1	39	1	19	116	148.011	0.202627	0.14144	0.882716	0.0740741	112	49014804	-	-	-	-	-	10	1	6
4	35-40	15-20	T3NF	PRESIDENT	Recruits with >1 to <5 Yrs Left on EFL	>$350m	AJ	ABCD CENTRAL	0.102297	0.0211128	0.0214352	0.110645	0.0941928	0.0530022	0	0.000444709	0.0276821	0.423894	0.0245316	0	0.120763	54526.7	11253.6	11425.5	58976.1	50206.9	28251.4	0	237.04	14755.2	225945	13075.9	0	64369.4	0.0693424	0.0375341	0.000474557	0.505691	0.278984	0.01328	0.135374	0.014328	0.0143347	2.82955e+07	1.5316e+07	193645	2.0635e+08	1.13841e+08	5.41897e+06	5.52399e+07	5.84663e+06	5.84936e+06	3.25	6.5	3.04	0.65	2.58	1.2	-	6414463	-3319428	190608	no action	Blue	B7	-692298	1	64	0	NaN	132	317.46	0.0338489	0.0358365	0.644886	0.0198864	71	533121	0.1361	0.1191	0.2042	0.2638	0.2765	1	7	1

	FA Details-FAID	FA Details-I-LOS	FA Details-Rocket-LOS	FA Details-Yrs. Left on EFL (Feb 2018)	FA Details-T12 (Feb 2018)	FA Details-AuM YE 17	Product Mix (Rev)-Advisory %	Product Mix (Rev)-Alts.	Product Mix (Rev)-BANKING	Product Mix (Rev)-CREDIT LINE	Product Mix (Rev)-EQUITIES	Product Mix (Rev)-INSURANCE / ANNUITIES	Product Mix (Rev)-LENDING	Product Mix (Rev)-MISCELLANEOUS	Product Mix (Rev)-MUNICIPALS	Product Mix (Rev)-MF and UIT	Product Mix (Rev)-OTHER	Product Mix (Rev)-STRUCTURED PROD	Product Mix (Rev)-TAXABLE FIXED I	Product Mix (Rev)-ADVISORY	Product Mix (Rev)-Alts..1	Product Mix (Rev)-BANKING.1	Product Mix (Rev)-CREDIT LINE.1	Product Mix (Rev)-EQUITIES.1	Product Mix (Rev)-INSURANCE / ANNUITIES.1	Product Mix (Rev)-LENDING.1	Product Mix (Rev)-MISCELLANEOUS.1	Product Mix (Rev)-MUNICIPALS.1	Product Mix (Rev)-MF and UIT.1	Product Mix (Rev)-OTHER.1	Product Mix (Rev)-STRUCTURED PROD.1	Product Mix (Rev)-TAXABLE FIXED I.1	Product Mix (Assets)-Wrap %	Product Mix (Assets)-CASH	Product Mix (Assets)-COMMODITIES	Product Mix (Assets)-EQUITY	Product Mix (Assets)-FIXED INCOME	Product Mix (Assets)-NON-TRADITIONAL	Product Mix (Assets)-OTHER	Product Mix (Assets)-OUTSIDE	Product Mix (Assets)-PB ASSET	Product Mix (Assets)-THIRD PARTY	Product Mix (Assets)-Wrap	Product Mix (Assets)-CASH .1	Product Mix (Assets)-COMMODITIES.1	Product Mix (Assets)-EQUITY.1	Product Mix (Assets)-FIXED INCOME.1	Product Mix (Assets)-NON-TRADITIONAL.1	Product Mix (Assets)-OTHER.1	Product Mix (Assets)-OUTSIDE.1	Product Mix (Assets)-PB ASSET.1	Product Mix (Assets)-THIRD PARTY.1	Product Penetration-H/UHNW MHHs with 4+ Products (%)	Product Penetration-H/UHNW MHHs with 4+ Products (#)	Client Acquisition Details-QNR 2015	Client Acquisition Details-QNR 2016	Client Acquisition Details-QNR 2017	Client Acquisition Details-3 year cumm. QNR	Client Acquisition Details-$1M+ QNR's 2015	Client Acquisition Details-$1M+ QNR's 2016	Client Acquisition Details-$1M+ QNR's 2017	Client Acquisition Details-3 year cumm. $1m+ QNR	Client Acquisition Details-YTD $1m+ QNRs (March-2018)	Net New Money Details-FA Comp. NNM 2014	Net New Money Details-FA Comp. NNM 2015	Net New Money Details-FA Comp. NNM 2016	Net New Money Details-FA Comp. NNM 2017	Net New Money Details-Magnitude of 2017 Growth	Net New Money Details-# of NNM Years +	Net New Money Details-Rate of Increase / decrease	Net New Money Details-YTD NNM (March-2018)	Maketing Details-Page Views	Maketing Details-News Feed Activity	Maketing Details-Total AMC orders (12 month)	Wealth/Financial Planning -Total MHHs	Wealth/Financial Planning -MHHs with a Plan	Wealth/Financial Planning -Total MHH Planning %	Wealth/Financial Planning -H/UHNW MHH Planning %	Client Retention-% of Attrited MHHs (2017 - Post Split)	Digital Tools-% of MHHs on OLS	-% of MHHs on MTP	-# accts enrolled in MTP	-Outside Assets in MTP	Banking and Lending-Cr Cards: H/UHNW MHHs Active CCs (%)	Banking and Lending-Cr Cards: H/UHNW MHHs Active CCs ( #)	Banking and Lending-Mortgages: H/UHNW MHHs w/ Rocket Jumbo (%)	Banking and Lending-Mortgages: H/UHNW MHHs w/ Rocket Jumbo (#)	Banking and Lending-SBL: H/UHNW MHHs Approved CLs (%)	Banking and Lending-SBL: H/UHNW MHHs Approved CLs (#)	Banking and Lending-SBL: H/UHNW MHHs Utilized CLs (%)	Banking and Lending-SBL: H/UHNW MHHs Utilized CLs (#)	Client Age Distribution (% of MHHs by Age)-<45 yrs.	Client Age Distribution (% of MHHs by Age)-45-54 yrs.	Client Age Distribution (% of MHHs by Age)-55-64 yrs.	Client Age Distribution (% of MHHs by Age)-65-74 yrs.	Client Age Distribution (% of MHHs by Age)-75+ yrs.	Segment Index Score (All FA Score)-Advisory Score	Segment Index Score (All FA Score)-Client Retention	Segment Index Score (All FA Score)-Marketing Score	Segment Index Score (All FA Score)-Planning Score	Segment Index Score (All FA Score)-Client Acquisition Score	Segment Index Score (All FA Score)-Banking Score	Segment Index Score (All FA Score)-Delivering the Firm
count	4186.000000	4186.000000	4186.000000	4186.000000	4.186000e+03	4.186000e+03	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4.186000e+03	4186.000000	4186.000000	4.186000e+03	4.186000e+03	4.186000e+03	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4.186000e+03	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4.186000e+03	4.186000e+03	4.186000e+03	4.186000e+03	4.186000e+03	4.186000e+03	4.186000e+03	4.186000e+03	4.186000e+03	4.186000e+03	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4.186000e+03	4.186000e+03	4.186000e+03	4.186000e+03	4186.000000	4186.000000	4186.000000	4.186000e+03	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4.186000e+03	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.00000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000	4186.000000
mean	2093.500000	23.466911	13.587795	1.730199	1.134682e+06	1.877151e+08	0.592554	0.016030	0.022049	0.067826	0.082322	0.070727	0.002605	0.006610	0.015872	0.088505	-0.009541	0.023561	0.016102	1.791602e+05	5509.182136	5463.874615	2.125237e+04	2.088383e+04	1.517874e+04	863.816677	1367.465024	4817.949221	22910.674467	824.894584	8169.467778	5.302650e+03	0.423425	0.086892	0.002212	0.462685	0.200011	0.021655	0.093564	0.036843	0.001309	0.084317	7.100390e+07	1.722293e+07	4.040478e+05	8.624269e+07	3.977456e+07	3.873096e+06	1.358131e+07	8.020352e+06	4.195648e+05	1.485875e+07	0.121143	9.337793	2.013292	1.924052	2.123378	6.060721	0.636696	0.686340	0.802052	2.125088	0.182946	5.020122e+06	9.453619e+05	-4.497897e+05	1.046323e+06	-0.007066	2.007406	0.160774	8.259749e+05	74.797420	16.851409	32.754897	252.304340	45.735786	0.168574	0.293041	0.060342	0.725762	0.031552	75.096034	1.145569e+07	0.140380	11.112996	0.054512	3.672002	0.355289	29.084329	0.141634	10.605112	0.097426	0.135158	0.239296	0.269914	0.247562	5.35571	5.500478	3.879479	5.192069	4.425466	4.923196	3.151457
std	1208.538442	10.689331	9.362084	3.645099	8.973850e+05	3.263091e+08	0.303882	0.038649	0.024694	0.070217	0.150668	0.649409	0.009533	0.076540	0.049944	0.123687	0.654831	0.070751	0.056745	1.675225e+05	17612.639859	4581.643418	4.207255e+04	4.536058e+04	2.852505e+04	3468.812069	16461.261243	22706.528414	32972.331985	14503.368542	30172.388627	5.063694e+04	0.220394	0.068418	0.006184	0.167008	0.122419	0.035993	0.087782	0.094407	0.024352	0.117869	7.348314e+07	7.976501e+07	4.210454e+06	1.623585e+08	1.727208e+08	9.298224e+06	1.525073e+07	3.974794e+07	9.354236e+06	2.726071e+07	0.135265	16.062589	2.383798	2.326492	2.609115	6.150614	1.018739	1.102379	1.391223	2.787934	0.422189	3.118790e+07	1.311712e+07	1.259914e+07	1.425041e+07	0.107797	1.327296	1.588098	6.070481e+06	118.762931	17.572173	57.815069	264.315857	63.591900	0.152802	0.225260	0.057395	0.123570	0.037324	149.413416	2.750145e+07	0.101627	13.142410	0.085518	6.249390	0.151832	32.748487	0.105026	11.841932	0.068272	0.074148	0.071655	0.075977	0.094194	2.98292	2.876697	2.280053	3.047119	2.963104	2.322861	3.168391
min	1.000000	1.010000	3.040000	-26.700000	-7.370890e+03	0.000000e+00	-5.897402	-0.078545	-0.294981	-0.001233	-1.341586	-3.813838	-0.024977	-0.655169	-0.000031	-1.973655	-40.916563	-0.002004	-0.001388	-6.483173e+04	-0.370000	-60.560000	-5.648000e+03	-1.996830e+03	-2.713566e+04	-10050.840000	-130552.880000	-0.030000	-13685.430000	-130555.240000	-288.750000	-2.957800e+02	0.000000	-0.008169	-0.000003	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000e+00	-3.687448e+05	-1.650000e+02	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	-4.172637e+08	-1.150899e+08	-1.746171e+08	-1.336483e+08	-2.275584	0.000000	-3.000000	-5.089051e+07	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000e+00	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000
25%	1047.250000	15.080000	7.070000	-0.100000	6.003767e+05	8.194347e+07	0.469694	0.000000	0.011744	0.023969	0.012604	0.009744	0.000000	0.000000	0.000000	0.023475	-0.014227	0.000000	0.000095	7.734394e+04	0.000000	2574.602500	4.678642e+03	2.912052e+03	2.209198e+03	0.000000	0.000000	0.000000	5075.497500	-3196.935000	0.000000	2.245500e+01	0.266040	0.048436	0.000116	0.360919	0.120895	0.002319	0.034551	0.000000	0.000000	0.006182	2.712665e+07	5.052046e+06	1.458889e+04	3.270600e+07	1.210773e+07	2.782678e+05	4.435908e+06	0.000000e+00	0.000000e+00	7.585299e+05	0.016300	1.000000	0.500000	0.500000	0.500000	2.010000	0.000000	0.000000	0.000000	0.400000	0.000000	-2.413674e+06	-3.236750e+06	-4.357734e+06	-3.856956e+06	-0.034644	1.000000	-1.000000	-9.287487e+05	0.000000	0.000000	1.000000	105.042017	7.000000	0.051700	0.106185	0.032306	0.651660	0.010101	10.000000	5.567155e+05	0.076900	3.000000	0.000000	0.000000	0.254895	10.000000	0.074000	3.000000	0.057900	0.092500	0.200700	0.231850	0.190500	3.00000	3.000000	2.250000	2.500000	2.000000	3.250000	1.000000
50%	2093.500000	23.070000	9.070000	1.730199	9.142914e+05	1.356191e+08	0.642321	0.002123	0.018699	0.050237	0.033392	0.033085	0.000000	0.000251	0.000651	0.053634	-0.000546	0.000000	0.002138	1.396902e+05	491.490000	4471.315000	1.185382e+04	8.043830e+03	7.560775e+03	0.000000	62.305000	160.880000	12654.310000	-102.710000	0.000000	5.339400e+02	0.432785	0.072903	0.000529	0.471014	0.185052	0.009950	0.070819	0.006037	0.000000	0.039533	5.182823e+07	9.416049e+06	7.581440e+04	5.863421e+07	2.380824e+07	1.272869e+06	9.378969e+06	8.569251e+05	0.000000e+00	5.120759e+06	0.076463	4.000000	1.400000	1.200000	1.500000	4.500000	0.020000	0.250000	0.400000	1.300000	0.000000	8.358655e+05	0.000000e+00	-7.035080e+05	-3.680700e+04	0.000579	2.000000	1.000000	8.768721e+04	42.000000	19.000000	6.000000	190.246116	24.000000	0.125900	0.249997	0.049037	0.734715	0.022075	37.000000	3.545754e+06	0.124967	7.000000	0.032200	2.000000	0.346908	20.000000	0.122717	7.000000	0.086200	0.123000	0.237200	0.269850	0.249100	5.00000	6.000000	3.750000	5.250000	4.500000	5.000000	2.000000
75%	3139.750000	32.030000	18.020000	4.900000	1.393252e+06	2.190662e+08	0.760473	0.015043	0.027762	0.091843	0.088712	0.076424	0.000000	0.001768	0.009831	0.109603	0.014002	0.006008	0.010388	2.340024e+05	3980.187500	7112.440000	2.566844e+04	2.126286e+04	1.883385e+04	0.000000	434.895000	2663.510000	28078.447500	3350.685000	1600.000000	2.633168e+03	0.583940	0.104221	0.001738	0.570725	0.256653	0.027569	0.130015	0.029904	0.000000	0.111762	9.182179e+07	1.787544e+07	2.617257e+05	1.008112e+08	4.268719e+07	4.024414e+06	1.702344e+07	4.442165e+06	0.000000e+00	1.643100e+07	0.184030	11.000000	2.930000	2.650000	3.000000	8.000000	1.000000	1.000000	1.000000	3.000000	0.010000	5.704661e+06	4.163891e+06	2.774054e+06	4.708356e+06	0.028563	3.000000	1.000000	1.688270e+06	95.000000	25.000000	45.000000	325.259921	59.000000	0.245125	0.446311	0.071736	0.810995	0.039810	88.000000	1.165661e+07	0.183563	15.000000	0.068096	5.000000	0.449950	37.000000	0.187500	14.000000	0.121900	0.163600	0.275500	0.309175	0.307000	8.00000	8.000000	5.500000	8.000000	6.500000	6.750000	5.750000
max	4186.000000	60.040000	57.090000	8.800000	1.655433e+07	1.462891e+10	10.418157	0.900247	1.000000	1.003966	4.537554	41.254047	0.149977	2.899751	0.927639	3.357692	5.560309	1.561850	0.960713	2.791913e+06	444474.840000	73104.660000	1.852232e+06	1.363943e+06	1.008435e+06	69530.350000	631978.510000	758347.500000	473056.320000	408775.930000	445300.000000	2.879387e+06	1.243781	1.000000	0.150654	1.000000	0.984384	0.701090	1.000000	1.000000	0.957373	0.929401	1.172262e+09	4.662406e+09	2.658435e+08	8.174091e+09	9.883264e+09	2.241594e+08	1.908794e+08	1.034103e+09	4.521319e+08	4.784023e+08	1.000000	213.000000	32.850000	29.480000	63.000000	94.050000	13.120000	14.380000	42.000000	45.100000	5.950000	9.624734e+08	1.474597e+08	9.893273e+07	2.298065e+08	1.029644	4.000000	3.000000	7.436511e+07	1467.000000	108.000000	764.000000	7037.037037	957.000000	1.000000	1.000000	1.000000	1.000000	0.588710	5400.000000	5.893744e+08	1.000000	246.000000	1.000000	71.000000	1.000000	694.000000	1.000000	107.000000	1.000000	1.000000	1.000000	1.000000	1.000000	10.00000	10.000000	10.000000	10.000000	10.000000	10.000000	10.000000

Data Cleaning¶

We see that there are many missing values in certain columns. To deal with that, there are a couple of ways:¶

Check for outliers¶

Features with constant values¶

EDA (exploratory data analysis)¶

Target variable here is "Net New Money Details-YTD NNM \n(March-2018)"¶

Analysis of trends in NNM Year by Year¶

<font color= 'blue'>From the joint plots, we can there are some extreme values at the edge of the distribution, indicating these are rare events. Generally these values will be problematic for linear models as they are sensitive to outliers.</font>¶

Analysis of FA details (features) influence on the target¶

Observation: -ve correaltion shows --> with increase in Industry length of service(I LOS) NNM decreases¶

Observation:
1) FA with FA Details-I-LOS Band = 10-15 years show best results
2) FA in 40 years I-LOS Band have the worst result

Observation: FA shows decreasing NNM trend with increase in length of service¶

<font color ='red'> Observation: Generally FA with assests of worth 150m-350m worth under them bring more money </font>¶

Analysis of Client acquisition details features influence on the target¶

<font color ='red'> Observation: All the client acquisition variables show positive correaltion</font>¶

Analysis of Marketing details of the FA¶

<font color ='red'> Observation: FA on FA web performer better</font>¶

Segment Index Score (All FA Score)¶

<font color ='red'> Observation: All features in the segment index score show positive correlation</font>¶

Correlation of numerical features with the target¶

Correlation of categorical features with the target¶

Observation: We observe weak correlation of these categorical variables with the target.This captures only linear dependency of each feature with the target. We will look at other model-based methods to evaluate feature importance later on.¶

Deal with collinear features¶

	column_name	null_count	null_%
0	Client Acquisition Details-YTD $1m+ QNRs (Marc...	3033	72.455805
1	FA Details-Rec. Council 2018	2831	67.630196
2	FA Details-Sig Team.	2062	49.259436
3	Client Acquisition Details-$1M+ QNR's 2015	2022	48.303870
4	Client Acquisition Details-$1M+ QNR's 2016	1856	44.338270
5	Client Acquisition Details-$1M+ QNR's 2017	1643	39.249881
6	Maketing Details-Page Views	1375	32.847587
7	Maketing Details-News Feed Activity	1172	27.998089
8	Client Acquisition Details-QNR 2017	761	18.179646
9	FA Details-Yrs. Left on EFL\n(Feb 2018)	368	8.791209
10	Wealth/Financial Planning -H/UHNW MHH Planning %	308	7.357860
11	Segment Index Score (All FA Score)-Planning Score	297	7.095079
12	Wealth/Financial Planning -Total MHHs	212	5.064501
13	Segment Index Score (All FA Score)-Advisory Score	100	2.388915
14	Product Mix (Assets)-Wrap %	97	2.317248
15	Product Mix (Assets)-Wrap	84	2.006689
16	Product Mix (Assets)-THIRD PARTY	44	1.051123
17	Product Mix (Assets)-PB ASSET	44	1.051123
18	Product Mix (Assets)-OUTSIDE	44	1.051123
19	Product Mix (Assets)-OTHER	44	1.051123
20	Product Mix (Assets)-NON-TRADITIONAL	44	1.051123
21	Product Mix (Assets)-FIXED INCOME	44	1.051123
22	Product Mix (Assets)-COMMODITIES	44	1.051123
23	Product Mix (Assets)-CASH	44	1.051123
24	Product Mix (Assets)-EQUITY	44	1.051123
25	Digital Tools-% of MHHs on OLS	43	1.027234
26	-% of MHHs on MTP	43	1.027234
27	-# accts enrolled in MTP	43	1.027234
28	-Outside Assets in MTP	43	1.027234
29	Client Age Distribution (% of MHHs by Age)-65-...	41	0.979455
30	Client Age Distribution (% of MHHs by Age)-75+...	41	0.979455
31	Client Age Distribution (% of MHHs by Age)-45-...	41	0.979455
32	Client Age Distribution (% of MHHs by Age)-55-...	41	0.979455
33	Client Age Distribution (% of MHHs by Age)-<45...	41	0.979455
34	Maketing Details-Total AMC orders (12 month)	38	0.907788
35	Net New Money Details-YTD NNM \n(March-2018)	30	0.716675
36	Product Mix (Assets)-FIXED INCOME.1	29	0.692785
37	Product Mix (Assets)-COMMODITIES.1	29	0.692785
38	Product Mix (Assets)-EQUITY.1	29	0.692785
39	Product Mix (Assets)-OTHER.1	29	0.692785
40	Product Mix (Assets)-NON-TRADITIONAL.1	29	0.692785
41	Product Mix (Assets)-PB ASSET.1	29	0.692785
42	Product Mix (Assets)-THIRD PARTY.1	29	0.692785
43	Product Mix (Assets)-CASH .1	29	0.692785
44	Product Mix (Assets)-OUTSIDE.1	29	0.692785
45	Product Mix (Rev)-MF and UIT	20	0.477783
46	Product Mix (Rev)-LENDING	20	0.477783
47	Product Mix (Rev)-OTHER	20	0.477783
48	Product Mix (Rev)-TAXABLE FIXED I	20	0.477783
49	Product Mix (Rev)-STRUCTURED PROD	20	0.477783
50	Product Mix (Rev)-INSURANCE / ANNUITIES	20	0.477783
51	Product Mix (Rev)-MISCELLANEOUS	20	0.477783
52	Product Mix (Rev)-MUNICIPALS	20	0.477783
53	Product Mix (Rev)-Advisory %	20	0.477783
54	Product Mix (Rev)-Alts.	20	0.477783
55	Product Mix (Rev)-BANKING	20	0.477783
56	Product Mix (Rev)-CREDIT LINE	20	0.477783
57	Product Mix (Rev)-EQUITIES	20	0.477783
58	Product Mix (Rev)-ADVISORY	18	0.430005
59	Product Mix (Rev)-Alts..1	18	0.430005
60	Product Mix (Rev)-BANKING.1	18	0.430005
61	Product Mix (Rev)-CREDIT LINE.1	18	0.430005
62	Product Mix (Rev)-EQUITIES.1	18	0.430005
63	Product Mix (Rev)-INSURANCE / ANNUITIES.1	18	0.430005
64	Product Mix (Rev)-LENDING.1	18	0.430005
65	Product Mix (Rev)-MUNICIPALS.1	18	0.430005
66	Product Mix (Rev)-STRUCTURED PROD.1	18	0.430005
67	Product Mix (Rev)-MISCELLANEOUS.1	18	0.430005
68	Product Mix (Rev)-TAXABLE FIXED I.1	18	0.430005
69	Product Mix (Rev)-OTHER.1	18	0.430005
70	Product Mix (Rev)-MF and UIT.1	18	0.430005
71	Branch Detail-Branch Wire	12	0.286670
72	Client Retention-% of Attrited MHHs (2017 - Po...	6	0.143335
73	Segment Index Score (All FA Score)-Client Rete...	5	0.119446
74	Client Acquisition Details-QNR 2015	4	0.095557
75	Client Acquisition Details-QNR 2016	2	0.047778
76	Net New Money Details-FA Comp. NNM 2016	2	0.047778
77	Net New Money Details-FA Comp. NNM 2015	2	0.047778
78	Net New Money Details-FA Comp. NNM 2014	2	0.047778
79	Segment Index Score (All FA Score)-Marketing S...	0	0.000000
80	Segment Index Score (All FA Score)-Client Acqu...	0	0.000000
81	Segment Index Score (All FA Score)-Banking Score	0	0.000000
82	Banking and Lending-Cr Cards: H/UHNW MHHs Acti...	0	0.000000
83	Banking and Lending-Cr Cards: H/UHNW MHHs Acti...	0	0.000000
84	Banking and Lending-Mortgages: H/UHNW MHHs w/ ...	0	0.000000
85	Banking and Lending-Mortgages: H/UHNW MHHs w/ ...	0	0.000000
86	Banking and Lending-SBL: H/UHNW MHHs Approved ...	0	0.000000
87	Banking and Lending-SBL: H/UHNW MHHs Utilized ...	0	0.000000
88	Banking and Lending-SBL: H/UHNW MHHs Utilized ...	0	0.000000
89	Banking and Lending-SBL: H/UHNW MHHs Approved ...	0	0.000000
90	Wealth/Financial Planning -Total MHH Planning %	0	0.000000
91	FA Details-FAID	0	0.000000
92	Wealth/Financial Planning -MHHs with a Plan	0	0.000000
93	Maketing Details-FA Social	0	0.000000
94	FA Details-Rocket-LOS	0	0.000000
95	FA Details-I-LOS Band	0	0.000000
96	FA Details-Rocket-LOS Band	0	0.000000
97	FA Details-Teaming Flag	0	0.000000
98	FA Details-Rocket Lifecycle Stage	0	0.000000
99	FA Details-Production Quintile	0	0.000000
100	FA Details-T12\n(Feb 2018)	0	0.000000
101	FA Details-AuM YE 17	0	0.000000
102	FA Details-AuM YE Band	0	0.000000
103	Branch Detail-Division	0	0.000000
104	FA Details-I-LOS	0	0.000000
105	Product Penetration-H/UHNW MHHs with 4+ Produc...	0	0.000000
106	Product Penetration-H/UHNW MHHs with 4+ Produc...	0	0.000000
107	Client Acquisition Details-3 year cumm. QNR	0	0.000000
108	Client Acquisition Details-3 year cumm. $1m+ QNR	0	0.000000
109	Net New Money Details-FA Comp. NNM 2017	0	0.000000
110	Net New Money Details-Magnitude of 2017 Growth	0	0.000000
111	Net New Money Details-# of NNM Years +	0	0.000000
112	Net New Money Details-Rate of Increase / decrease	0	0.000000
113	Net New Money Details-2017 Break in NNM	0	0.000000
114	Net New Money Details-Color	0	0.000000
115	Net New Money Details-Identifier	0	0.000000
116	Maketing Details-FA Web	0	0.000000
117	Segment Index Score (All FA Score)-Delivering ...	0	0.000000

	column name	skewness %
0	Net New Money Details-FA Comp. NNM 2014	8.361760
1	Net New Money Details-FA Comp. NNM 2017	2.537556
2	Net New Money Details-YTD NNM \n(March-2018)	2.502333
3	Net New Money Details-FA Comp. NNM 2015	1.423099
4	Net New Money Details-# of NNM Years +	0.016480
5	Net New Money Details-FA Comp. NNM 2016	-0.242030
6	Net New Money Details-Rate of Increase / decrease	-0.285138
7	Net New Money Details-Magnitude of 2017 Growth	-3.652830

	VIF Factor	features
0	inf	Net New Money Details-Color_Green
1	inf	Net New Money Details-Color_Orange
2	inf	Net New Money Details-Color_Red
3	1.000000	Net New Money Details-Identifier_B2
4	1.000000	Net New Money Details-Identifier_B3
5	1.000000	Net New Money Details-Identifier_B4
6	1.000000	Net New Money Details-Identifier_B5
7	1.000000	Net New Money Details-Identifier_B6
8	1.000000	Net New Money Details-Identifier_B7
9	1.000000	Net New Money Details-Identifier_B8
10	1.000000	Net New Money Details-Identifier_B9
11	inf	Net New Money Details-Identifier_G1
12	inf	Net New Money Details-Identifier_G2
13	inf	Net New Money Details-Identifier_G3
14	inf	Net New Money Details-Identifier_G4
15	inf	Net New Money Details-Identifier_G5
16	inf	Net New Money Details-Identifier_G6
17	inf	Net New Money Details-Identifier_O1
18	inf	Net New Money Details-Identifier_O2
19	inf	Net New Money Details-Identifier_O3
20	inf	Net New Money Details-Identifier_O4
21	inf	Net New Money Details-Identifier_O5
22	inf	Net New Money Details-Identifier_O6
23	inf	Net New Money Details-Identifier_O7
24	inf	Net New Money Details-Identifier_O8
25	inf	Net New Money Details-Identifier_O9
26	inf	Net New Money Details-Identifier_R1
27	inf	Net New Money Details-Identifier_R2
28	inf	Net New Money Details-Identifier_R3
29	inf	Net New Money Details-Identifier_R4
30	inf	Net New Money Details-Identifier_R5
31	inf	Net New Money Details-Identifier_R6

	Net New Money Details-Color_Green	Net New Money Details-Color_Orange	Net New Money Details-Color_Red	Net New Money Details-Identifier_B3	Net New Money Details-Identifier_B4	Net New Money Details-Identifier_B7	Net New Money Details-Identifier_B8	Net New Money Details-Identifier_B9	Net New Money Details-Identifier_G1	Net New Money Details-Identifier_G2	Net New Money Details-Identifier_G3	Net New Money Details-Identifier_G5	Net New Money Details-Identifier_G6	Net New Money Details-Identifier_O1	Net New Money Details-Identifier_O3	Net New Money Details-Identifier_O5	Net New Money Details-Identifier_O6	Net New Money Details-Identifier_R1	Net New Money Details-Identifier_R3	Net New Money Details-Identifier_R6
0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
2	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
7	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
8	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
9	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
10	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
11	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
12	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
13	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
14	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
15	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
16	0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
17	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
18	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
19	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
20	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
21	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
22	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
23	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
24	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
25	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
26	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
27	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
28	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
29	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
30	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
31	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
32	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
33	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
34	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
35	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
36	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
37	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
38	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
39	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0

Data Cleaning¶

We see that there are many missing values in certain columns. To deal with that, there are a couple of ways:¶

Check for outliers¶

Features with constant values¶

EDA (exploratory data analysis)¶

Target variable here is "Net New Money Details-YTD NNM \n(March-2018)"¶

Analysis of trends in NNM Year by Year¶

<font color= 'blue'>From the joint plots, we can there are some extreme values at the edge of the distribution, indicating these are rare events. Generally these values will be problematic for linear models as they are sensitive to outliers.</font>¶

<font color= 'blue'>There are two categorical features in NNM and they seem to be related based on the column names. To confirm this, we will look at the variance inflation factor (VIF) score of these features</font>¶

Analysis of FA details (features) influence on the target¶

Observation: -ve correaltion shows --> with increase in Industry length of service(I LOS) NNM decreases¶

Observation: 1) FA with FA Details-I-LOS Band = 10-15 years show best results 2) FA in 40 years I-LOS Band have the worst result

Observation: FA shows decreasing NNM trend with increase in length of service¶

<font color ='red'> Observation: Generally FA with assests of worth 150m-350m worth under them bring more money </font>¶

Analysis of Client acquisition details features influence on the target¶

<font color ='red'> Observation: All the client acquisition variables show positive correaltion</font>¶

Analysis of Marketing details of the FA¶

<font color ='red'> Observation: FA on FA web performer better</font>¶

<font color ='red'> Observation: FA not on social media perform better</font>¶

Segment Index Score (All FA Score)¶

<font color ='red'> Observation: All features in the segment index score show positive correlation</font>¶

Correlation of numerical features with the target¶

Correlation of categorical features with the target¶

Observation: We observe weak correlation of these categorical variables with the target.This captures only linear dependency of each feature with the target. We will look at other model-based methods to evaluate feature importance later on.¶

Deal with collinear features¶

Observation:
1) FA with FA Details-I-LOS Band = 10-15 years show best results
2) FA in 40 years I-LOS Band have the worst result